Introduction

To manage your data in a relational database, you must have a good understanding of how to access, extract, process and manipulate information using various SQL queries.

In this reading you’ll recap various types of SQL queries and learn more about triggers and transactions in MySQL.

Overview of SQL queries

Basic and complex SQL queries

To extract all existing data from a table, you can use the primary form of a SQL query as follows:

SELECT * FROM table; 

If you need to perform more specific queries, then you may need to filter data, and use operators, alias and aggregate functions.

For instance, you may need to extract specific information from your database. In this instance, you may need to write SQL queries to obtain data from a table with specific dates as demonstrated in the following basic syntax.

SELECT column_name 
FROM table_name 
WHERE date_column > date_value;

A liases

In some cases, you might find that existing data has been labelled with long, complicated or less meaningful names, which may confuse database users. In this case, you can use an alias to display a particular column with a more meaningful name.

The use of an alias can also be very important when working with JOIN statements.

An alias is created using the AS keyword as shown in the following syntax:

SELECT column_a AS 'Column A' FROM table;

Aggregate functions

You can also use aggregate functions to summarize data in your database such as COUNT , SUM , MIN , MAX , and AVERAGE .

For example, you can use the following syntax to count all values in a particular column.

SELECT COUNT(column_name) 
FROM table_name;

To perform more sophisticated SQL queries, you need to be familiar with more advanced SQL concepts such as subqueries, JOINS, virtual tables, stored procedures and prepared statements.

These topics have already been covered in previous items, in addition to triggers and transactions concepts, which are important when performing complex operations.

Triggers

Overview of triggers

A MySQL trigger is a set of actions available in the form of a stored program. The set of actions is invoked automatically when certain events occur. You can use triggers for different types of events, like inserting, deleting and updating data.

Triggers can be used in MySQL for several purposes such as:

Trigger syntax

To use a trigger, you first need to create it using the CREATE TRIGGER statement. Then define the trigger type. Is it an INSERT, UPDATE or DELETE trigger? And should it be executed BEFORE or AFTER the event?

You also need to define the trigger’s logic, specify which table it’s assigned to, and how it should be applied to the table.

An example of the syntax used to create a trigger in MySQL is as follows:

CREATE TRIGGER trigger_name 
{BEFORE | AFTER} {INSERT | UPDATE| DELETE} 
ON table_name FOR EACH ROW 
trigger_body;

The syntax to remove a MySQL trigger is as follows:

DROP TRIGGER [IF EXISTS] [schema_name] trigger_name

To learn more about Triggers, you can review the About triggers in MySQ L item in ‘Advanced MySQL Topics’ course.

Transactions

Overview of transactions

You should use MySQL transactions when carrying out critical activities, as they can be used to roll your transaction back to its original state if any failure occurs.

This is especially important when writing a series of related queries that must all work together to achieve the expected outcome. If a specific query fails, then you can use the transaction statement to undo the changes that took place.

Transaction syntax

MySQL provides a set of statements to manage database transactions.

The START TRANSACTION statement is the standard SQL statement used to start a transaction process. The COMMIT statement is used to commit the current transaction and make the transaction changes permanently, as shown in the following example:

START TRANSACTION; 
SQL statements 
COMMIT; 

If you want to roll back the current transaction and cancel the changes made to the database, you can use the ROLLBACK statement as follows:

START TRANSACTION; 
SQL statements 
ROLLBACK; 

To learn more about transactions, you can review the MySQL Transaction item in the ‘Advanced MySQL Topics’ course.

Conclusion

In this reading, you recapped the basics of SQL queries, triggers and transactions. You are now ready to help Little Lemon develop their table booking system in this lesson’s exercises.